/**
 * Excel公用方法
 *  
 * @author 董一华 2010-08-22
 * 
 */
package com.zjht.gmcc.bean.file;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.zjht.gmcc.bean.util.MyFormat;

public class Excel {

	public static final short FONTSIZE_NORMAL = 10;
	public static final short FONTSIZE_SMALL = 8;

	public static final short COL_WIDTH_DEAULT = 11;// 列宽
	public static final short COL_WIDTH_DEFAULT_SMALL = 6;// 列宽(小)
	public static final short COL_WIDTH_MIDDLE = 2500;// 列宽(中)
	public static final short COL_WIDTH_SMALL_LESS = 1100;// 列宽(更小)
	public static final short COL_WIDTH_SMALL = 1600;// 列宽(小)

	public static final short ROW_HEIGHT = 25;// 行高

	// 单元格类型
	public static enum CellType {
		STRING, // 字符串
		INT, // 整数
		DOUBLE
		// 小数
	};
	
	/**
	 * 根据Excel规定，格式化sheetname(不能为空，不能为/\*?[])
	 * @author dyh 2010-08-23
	 * @param wb
	 * @return 经格式化后的sheetname
	 */
	public static String formatSheetName(String sheetname) {
		if (sheetname.length() > 31)
			sheetname = sheetname.substring(0, 31);
		return sheetname.replace('/', ' ').replace('\\', ' ').replace('*', ' ').replace('?', ' ').replace('[', ' ').replace(']', ' ');
	}

	/**
	 * 创建sheet
	 * @author dyh 2010-09-10
	 * @param wb
	 * @param sheetid
	 * @param sheetname
	 * @return
	 */
	public static HSSFSheet createSheet(HSSFWorkbook wb, int sheetid, String sheetname) {
		return createSheet(wb, sheetid, sheetname, false);
	}

	/**
	 * 创建sheet
	 * @author dyh 2010-09-10
	 * @param wb
	 * @param sheetid
	 * @param sheetname
	 * @param landscape 是否横向页面
	 * @return
	 */
	public static HSSFSheet createSheet(HSSFWorkbook wb, int sheetid, String sheetname, boolean landscape) {
		HSSFSheet sheet = wb.createSheet();
		if (!MyFormat.isStrNull(sheetname)) {
			sheetname = formatSheetName(sheetname);
			wb.setSheetName(sheetid, sheetname, HSSFWorkbook.ENCODING_UTF_16);
		}
		HSSFPrintSetup pss = sheet.getPrintSetup();
		pss.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
		pss.setLandscape(landscape);
		sheet.setHorizontallyCenter(true);
		sheet.setMargin(HSSFSheet.LeftMargin, 0.5);
		sheet.setMargin(HSSFSheet.RightMargin, 0.5);
		sheet.setMargin(HSSFSheet.TopMargin, 0.5);
		sheet.setMargin(HSSFSheet.BottomMargin, 0.5);
		sheet.setDefaultColumnWidth(landscape ? COL_WIDTH_DEFAULT_SMALL : COL_WIDTH_DEAULT);
		return sheet;
	}

	/**
	 * 创建字体
	 * @author dyh 2010-9-11
	 * @param wb
	 * @param bold 是否黑体
	 * @param fontsize 字体大小
	 * @return
	 */
	public static HSSFFont createFont(HSSFWorkbook wb, boolean bold, short fontsize) {
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints(fontsize);
		if (bold)
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		return font;
	}

	/**
	 * 创建Cell样式
	 * @author dyh 2010-9-11
	 * @param wb
	 * @param font
	 * @param isHeader
	 * @param center
	 * @param isNum
	 * @return
	 */
	public static HSSFCellStyle createStyle(HSSFWorkbook wb, HSSFFont font, boolean isHeader, boolean center, CellType cellType) {
		HSSFCellStyle style = wb.createCellStyle();
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		if (center)
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		if (isHeader)
			style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		else
			style.setFillForegroundColor(HSSFColor.WHITE.index);

		switch (cellType) {
			case STRING:
				break;
			case INT:
				style.setDataFormat(wb.createDataFormat().getFormat("##0"));
				break;
			case DOUBLE:
				style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
				break;
		}
		style.setFont(font);
		return style;
	}

	/**
	 * 创建行
	 * @author dyh 2010-9-11
	 * @param sheet
	 * @param rowIndex
	 * @return
	 */
	public static HSSFRow createRow(HSSFSheet sheet, short rowIndex) {
		HSSFRow row = sheet.createRow(rowIndex);
		row.setHeightInPoints(ROW_HEIGHT);
		return row;
	}

	/**
	 * 创建单元格
	 * @author dyh 2010-9-11
	 * @param style
	 * @param row
	 * @param colIndex
	 * @param value
	 * @return
	 */
	public static HSSFCell createCell(HSSFCellStyle style, HSSFRow row, short colIndex, String value) {
		HSSFCell cell = row.createCell(colIndex);
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(value);
		return cell;
	}

	/**
	 * 创建单元格
	 * @author dyh 2010-9-11
	 * @param style
	 * @param row
	 * @param colIndex
	 * @param value
	 * @return
	 */
	public static HSSFCell createCell(HSSFCellStyle style, HSSFRow row, short colIndex, int value) {
		HSSFCell cell = row.createCell(colIndex);
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(value);
		return cell;
	}

	/**
	 * 创建单元格
	 * @author dyh 2010-9-11
	 * @param style
	 * @param row
	 * @param colIndex
	 * @param value
	 * @return
	 */
	public static HSSFCell createCell(HSSFCellStyle style, HSSFRow row, short colIndex, double value) {
		HSSFCell cell = row.createCell(colIndex);
		cell.setCellStyle(style);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(value);
		return cell;
	}

}